﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace SensitivityTest.model
{
    class SQLData
    {
        public static string mServer;
        public static string connString="";
        public static int SelectScodeForConfig(string sqlString)
        {
            connString = sqlConnString();
            //SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            //scsb.DataSource = mServer;
            //scsb.InitialCatalog = "ATE";
            //scsb.UserID = "ClientDataAccesser";
            //scsb.Password = "mobi@20120510";
            SqlConnection SQLCONN = new SqlConnection(connString);
            SQLCONN.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlString, SQLCONN);
            int selectResult = (int)sqlcomm.ExecuteScalar();
            return selectResult;
        }

        public static void insertConfigData(string sqlString)
        {
            connString = sqlConnString();
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlString, sqlconn);
            sqlcomm.ExecuteNonQuery();
            sqlconn.Close();
        }

        public static DataTable SelectConfigData(string sqlString)
        {
            connString = sqlConnString();
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlString, sqlconn);
            SqlDataAdapter sqlada = new SqlDataAdapter(sqlcomm);
            DataTable dt = new DataTable();
            sqlada.Fill(dt);
            sqlconn.Close();
            return dt;
        }

        public static void UpdateConfigData(string sqlString)
        {
            connString = sqlConnString();
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlString, sqlconn);
            sqlcomm.ExecuteNonQuery();
            sqlconn.Close();
        }

        public static void InsertIntoData(Frm_Main fmain,out int id)
        {
            connString = sqlConnString();
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand("tb_Test_Record_Insert_OIP3", sqlconn);
            sqlcomm.CommandType = CommandType.StoredProcedure;
            sqlcomm.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output ;
            sqlcomm.Parameters.Add("@ProductSN", SqlDbType.VarChar).Value = (fmain.txt_SN.Text).Trim();
            sqlcomm.Parameters.Add("@RecordTime", SqlDbType.DateTime).Value = DateTime.Now;
            sqlcomm.Parameters.Add("@TestPeriod", SqlDbType.Int).Value = fmain.totalTime;
            sqlcomm.Parameters.Add("@ProductTypeID",SqlDbType.VarChar).Value=fmain.cmb_ProductName.Text;
            sqlcomm.Parameters.Add("@UserID",SqlDbType.VarChar).Value=fmain.txt_User.Text;
            sqlcomm.Parameters.Add("@TestEquipID",SqlDbType.VarChar).Value=MDIParent1.localIP;
            sqlcomm.Parameters.Add("@TestProjID",SqlDbType.VarChar).Value=fmain.cmb_Versions.Text;
            if (fmain.lbl_result.Text == "PASS")
            {
                sqlcomm.Parameters.Add("@Result", SqlDbType.Bit).Value = 1;
            }
            else
            {
                sqlcomm.Parameters.Add("@Result", SqlDbType.Bit).Value = 0;
            }
            sqlcomm.Parameters.Add("@Remark",SqlDbType.VarChar).Value="";
            sqlcomm.ExecuteNonQuery();
            id = int.Parse(sqlcomm.Parameters["@ID"].Value.ToString());
            sqlconn.Close();
        }

        public static void InsertIntoItemData(Frm_Main fmain,int id)
        {
            connString = sqlConnString();
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand("tb_TestItem_Record_Insert", sqlconn);
            sqlcomm.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i<fmain.dataGridView1.Rows.Count - 1; i++)
            { 
                sqlcomm.Parameters.Add("@ID", SqlDbType.Int).Value = id;
                sqlcomm.Parameters.Add("@ProductSN", SqlDbType.VarChar).Value =fmain.txt_SN.Text.Trim();
                sqlcomm.Parameters.Add("@RecordTime", SqlDbType.DateTime).Value = DateTime.Now;
                sqlcomm.Parameters.Add("@TestItemName", SqlDbType.VarChar).Value = fmain.dataGridView1.Rows[i].Cells["Chan"].Value + "通道 OIP3 Test";
                sqlcomm.Parameters.Add("@TestItemDesc", SqlDbType.VarChar).Value = fmain.dataGridView1.Rows[i].Cells["Chan"].Value + "," + fmain.dataGridView1.Rows[i].Cells["F1"].Value + "," + fmain.dataGridView1.Rows[i].Cells["F2"].Value;
                sqlcomm.Parameters.Add("@Spec", SqlDbType.VarChar).Value = fmain.dataGridView1.Rows[i].Cells["Spec"].Value;
                sqlcomm.Parameters.Add("@ResValue", SqlDbType.VarChar).Value = fmain.dataGridView1.Rows[i].Cells["testValue"].Value;
                if (fmain.lbl_result.Text == "PASS")
                {
                    sqlcomm.Parameters.Add("@Result", SqlDbType.Bit).Value = true;
                }
                else
                {
                    sqlcomm.Parameters.Add("@Result", SqlDbType.Bit).Value = false;
                }
                sqlcomm.ExecuteNonQuery();
                sqlcomm.Parameters.Clear();
            }
            sqlconn.Close();
        }

        public static int Test_IsvaildClientSoftVersion(string csName, string csVersion)
        {
            connString = sqlConnString();
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand("Test_IsvaildClientSoftVersion", sqlconn);
            sqlcomm.CommandType = CommandType.StoredProcedure;
            
            sqlcomm.Parameters.Add("@CSName", SqlDbType.VarChar).Value = csName;
            sqlcomm.Parameters.Add("@CSVersion", SqlDbType.VarChar).Value = csVersion;
            sqlcomm.Parameters.Add("@IsvaildPID", SqlDbType.Int).Direction = ParameterDirection.Output;

            sqlcomm.ExecuteNonQuery();
            int vaildResult = int.Parse(sqlcomm.Parameters["@IsvaildPID"].Value.ToString());
            sqlconn.Close();
            return vaildResult;
        }

        public static string sqlConnString()
        {
            return "server='" + mServer + "';uid='sa';pwd='mobi@20120510';database='ATE'";
        }
    }
}
